package com.sxt.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



import com.sxt.dao.AccountDao;
import com.sxt.entity.Account;

/**
 * 账户数据操作类，实现账户数据操作接口
 * @author lovemanai
 *
 */
public class AccountDaoImpl implements AccountDao {
	
	public Connection connectToDB() {
		
		Connection conn = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/account?user=root&password=123456");
		} catch(ClassNotFoundException e) {
			System.out.println("没有找到相应的驱动！");
			e.printStackTrace();
		} catch(SQLException e) {
			e.printStackTrace();
		} 
		return conn;
	}
	
	/*
	 * 添加账户
	 * 把一条记录插入到数据库中
	 */
	@Override
	public void addAccount(Account account, Connection conn) {
		PreparedStatement psta = null;
		try {
			psta = conn.prepareStatement("insert into account values(?,?,?,?,?,?,?)");
			psta.setString(1, account.getAccountNo());
			psta.setString(2, account.getPassword());
			psta.setDouble(3, account.getMoney());
			psta.setString(4, account.getUserName());
			psta.setString(5, account.getId());
			psta.setString(6, account.getPhoneNo());
			psta.setString(7, account.getAddress());
			psta.execute();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(psta != null) {
					psta.close();
					psta = null;
				}
			} catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		
	}


	/*
	 * 删除账户
	 * 在数据库中找到相应的记录删除之
	 */
	@Override
	public void removeAccount(Account account, Connection conn) {
		Statement sta = null;
		try {
			String sql = "delete from account where accountNo = " + "'" + account.getAccountNo() + "'";
			sta = conn.createStatement();
			sta.execute(sql);
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(sta != null) {
					sta.close();
					sta = null;
				}
			} catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		
	}

	/*
	 * 更新账户
	 * 只可以更改密码，手机号码，地址
	 */
	@Override
	public void updateAccount(Account account, Account accountTemp, Connection conn) {
		Statement sta = null;
		try {
			sta = conn.createStatement();
			String sql = "update account set " + 
					"passwd = " + "'" + accountTemp.getPassword() + "' ," +
					"phoneNo = " + "'" + accountTemp.getPhoneNo() + "' ," +
					"address = " + "'" + accountTemp.getAddress() + "' " +
					"where accountNo = " + "'" + account.getAccountNo()+ "'";
			sta.execute(sql);
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(sta != null) {
					sta.close();
					sta = null;
				}
			} catch(SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

	

	/*
	 * 查询账户
	 * 通过账号和密码
	 */
	@Override
	public Account searchAccount(Account account, Connection conn) {
		Statement sta = null;
		ResultSet rs = null;
		try {
			String sql = "select * from account where accountNo = " + "'" + account.getAccountNo() + "'" + 
						" and passwd = " + "'" + account.getPassword()+"'" ;
			sta = conn.createStatement();
			rs = sta.executeQuery(sql);
			if(!rs.next()) {
				return null;
			} else {
				account.setAccountNo(rs.getString("accountNo"));
				account.setPassword(rs.getString("passwd"));
				account.setMoney(rs.getDouble("money"));
				account.setUserName(rs.getString("userName"));
				account.setId(rs.getString("id"));
				account.setPhoneNo(rs.getString("phoneNo"));
				account.setAddress(rs.getString("address"));
			}
		}catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(sta != null) {
					sta.close();
					sta = null;
				}
				
			} catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		return account;
	}
	
	/*
	 * 查询账户
	 * 通过账号
	 * 
	 */
	@Override
	public Account searchAccountByAccountNo(Account account, Connection conn) {
		Statement sta = null;
		ResultSet rs = null;
		try {
			String sql = "select * from account where accountNo = " + "'" + account.getAccountNo() + "'";
			sta = conn.createStatement();
			rs = sta.executeQuery(sql);
			if(!rs.next()) {
				return null;
			} else {
				account.setAccountNo(rs.getString("accountNo"));
				account.setPassword(rs.getString("passwd"));
				account.setMoney(rs.getDouble("money"));
				account.setUserName(rs.getString("userName"));
				account.setId(rs.getString("id"));
				account.setPhoneNo(rs.getString("phoneNo"));
				account.setAddress(rs.getString("address"));
			}
		}catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(sta != null) {
					sta.close();
					sta = null;
				}
				
			} catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		return account;
	}
	
	
	/*
	 * 打印一个账户
	 */
	@Override
	public void printSingle(Account account, Connection conn) {
		Statement sta = null;
		ResultSet rs = null;
		try {
			String sql = "select * from account where accountNo = " + "'" + account.getAccountNo() + "'";
			sta = conn.createStatement();
			rs = sta.executeQuery(sql);
			if(rs.next()){
				System.out.println("账号：" + rs.getString("accountNo"));
				System.out.println("密码：" + rs.getString("passwd"));
				System.out.println("存款：" + rs.getString("money"));
				System.out.println("用户名：" + rs.getString("userName"));
				System.out.println("身份证号：" + rs.getString("id"));
				System.out.println("手机号码：" + rs.getString("phoneNo"));
				System.out.println("地址：" + rs.getString("address"));
			}
		}catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(sta != null) {
					sta.close();
					sta = null;
				}
				
			} catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
	}
	
	
	/*
	 * 打印所有账户
	 *
	 */
	@Override
	public void printAll(Connection conn) {
		Statement sta = null;
		ResultSet rs = null;
		
		int count = 1;
		
		try {
			String sql = "select * from account";
			sta = conn.createStatement();
			rs = sta.executeQuery(sql);
			while(rs.next()){
				System.out.println("第" + count + "个账户信息");
				System.out.println("账号：" + rs.getString("accountNo"));
				System.out.println("密码：" + rs.getString("passwd"));
				System.out.println("存款：" + rs.getString("money"));
				System.out.println("用户名：" + rs.getString("userName"));
				System.out.println("身份证号：" + rs.getString("id"));
				System.out.println("手机号码：" + rs.getString("phoneNo"));
				System.out.println("地址：" + rs.getString("address"));
				count++;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(sta != null) {
					sta.close();
					sta = null;
				}
				
			} catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		
	}
	
}
